1. Technical Field
This invention generally relates to computer systems, and more specifically relates to apparatus and methods for optimizing a database query.
2. Background Art
Since the dawn of the computer age, computers have evolved and become more and more powerful. In our present day, computers have become indispensable in many fields of human endeavor including engineering design, machine and process control, information storage and retrieval, and office computing. One of the primary uses of computers is for information storage and retrieval.
Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search for and retrieve specific information in the database. For example, an insurance company may have a database that includes all of its policy holders and their current account information, including payment history, premium amount, policy number, policy type, exclusions to coverage, etc. A database system allows the insurance company to retrieve the account information for a single policy holder among the thousands and perhaps millions of policy holders in its database.
Retrieval of information from a database is typically done using queries. A query usually specifies conditions that apply to one or more columns of the database, and may specify relatively complex logical operations on multiple columns. The database is searched for records that satisfy the query, and those records are returned as the query result.
Queries that are frequently run may be optimized to enhance system performance. One known way to optimize a query is to build an access plan the first time a query is run, then reuse the access plan for subsequent executions of the same query.
Graphical User Interfaces (GUIs) for databases have been developed that allow a user that is not familiar with Structured Query Language (SQL) to execute queries on a database. The GUI typically allows the user to specify values for columns in the database. Under the covers, the user-specified values are typically plugged into an SQL query, which is then executed to perform the user's query.
A very common coding practice for GUIs is to specify one large SQL query that contains predicates for all columns in all database tables referenced in the query. When a user specifies values in the GUI, these values are then plugged into the large SQL query. Any fields that the user leaves blank in the GUI will pass the wildcard, or some values that select every possible record. While this is a convenient and easy way to code a database GUI, it creates undue performance penalties in many cases because only one access plan is saved for the query, even though the query may take many different forms. The result is that the stored access plan may not be efficient to execute some variations of the query, resulting in performance penalties. Without a way to optimize a query in a way that does not create the performance penalties known in the art, the database industry will continue to suffer from undue performance penalties by reusing a single access plan for all variations of a query.